<?php
/**
 * PostgreSQL数据库操作
 * 
 * $Id: PgSQL.class.php 89 2009-08-25 07:40:55Z sunxy $
 */

class DB_Sql {
	var $Host     = "localhost";
	var $Database = "winhap";
	var $Port     = "5432";
	var $User     = "postgre";
	var $Password = "postgre";

	var $Link_ID  = 0;
	var $Query_ID = 0;
	var $Record   = array();
	var $Row      = 0;

	var $Seq_Table     = "db_sequence";

	var $Errno    = 0;
	var $Error    = "";
	var $Debug    = 0;

	var $Auto_Free = 0; # Set this to 1 for automatic pg_freeresult on 
						# last record.
	
	function DB_Sql() {
	}
	
	function ifadd($add, $me) {
		if ("" != $add) {
			return " " . $me . $add;
		}
	}
	
	function connect($Host = '', $Database = '', $User = '', $Password = '') {		
		$cstr = "dbname=" . $Database .
			$this->ifadd($Host, "host=") .
			$this->ifadd(5432, "port=") .
			$this->ifadd($User, "user=") .
			$this->ifadd($Password, "password=");
		
		$this->Link_ID = pg_connect($cstr);		
		if (!$this->Link_ID) {
			$this->halt("connect() failed.");
		}		
	}	

	function close() {
		return pg_close($this->Link_ID);
	}

	function query($Query_String) {
		if ($Query_String == "") {
			return 0;
		}
				
		if ($this->Debug) {
			printf("<br>Debug: query = %s<br>\n", $Query_String);
		}
		
		$this->Query_ID = pg_Exec($this->Link_ID, $Query_String);
		$this->Row = 0;

		$this->Error = pg_ErrorMessage($this->Link_ID);
		$this->Errno = ($this->Error == "")?0:1;
		
		if (!$this->Query_ID) {
			$this->halt("Invalid SQL: ".$Query_String);
		}
		
		return $this->Query_ID;
	}
  
	function next_record() {
		$this->Record = @pg_fetch_array($this->Query_ID, $this->Row++);
    
		$this->Error = pg_ErrorMessage($this->Link_ID);
		$this->Errno = ($this->Error == "")?0:1;

		$stat = is_array($this->Record);
		if (!$stat && $this->Auto_Free) {
			pg_freeresult($this->Query_ID);
			$this->Query_ID = 0;
		}
		return $stat;
	}

	function fetch_array($Query_ID, $result_type = PGSQL_ASSOC) {
		$this->Record = @pg_fetch_array($Query_ID, $this->Row++, $result_type);
    
		$this->Error = pg_ErrorMessage($this->Link_ID);
		$this->Errno = ($this->Error == "")?0:1;
		
		return $this->Record;
	}

	function seek($pos) {
		$this->Row = $pos;
	}

	function lock($table, $mode = "write") {
		if ($mode == "write") {
			return $this->query("lock table $table");
		}
		return 1;
	}
  
	function unlock() {
		return $this->query("commit");
	}

	/* public: sequence numbers */
	function nextid($seq_name) {
				
		if ($this->lock($this->Seq_Table)) {
			$q = sprintf("select nextid from %s where seq_name = '%s'",
				$this->Seq_Table,
				$seq_name);
			$id  = @pg_Exec($this->Link_ID, $q);
			$res = @pg_Fetch_Array($id, 0);
			
			
			if (!is_array($res)) {
				$currentid = 0;
				$q = sprintf("insert into %s values('%s', %s)",
					$this->Seq_Table,
					$seq_name,
					$currentid);
				$id = @pg_Exec($this->Link_ID, $q);
				} else {
					$currentid = $res["nextid"];
				}
				$nextid = $currentid + 1;
				$q = sprintf("update %s set nextid = '%s' where seq_name = '%s'",
					$this->Seq_Table,
					$nextid,
					$seq_name);
				$id = @pg_Exec($this->Link_ID, $q);
				$this->unlock();
			} else {
				$this->halt("cannot lock ".$this->Seq_Table." - has it been created?");
				return 0;
			}
		return $nextid;
	}

	function metadata($table) {
		$count = 0;
		$id    = 0;
		$res   = array();
		
		$id = pg_exec($this->Link_ID, "select * from $table");
		if ($id < 0) {
			$this->Error = pg_ErrorMessage($id);
			$this->Errno = 1;
			$this->halt("Metadata query failed.");
		}
		
		$count = pg_NumFields($id);
		
		for ($i = 0; $i < $count; $i++) {
			$res[$i]["table"] = $table;
			$res[$i]["name"]  = pg_FieldName  ($id, $i); 
			$res[$i]["type"]  = pg_FieldType  ($id, $i);
			$res[$i]["len"]   = pg_FieldSize  ($id, $i);
			$res[$i]["flags"] = "";
		}
		pg_FreeResult($id);
		return $res;
	}

	function limitQuery($sql, $from, $count) {
		$query = $this->query("$sql limit $count offset $from");
		return $query;
	}
	
	function affected_rows() {
		return pg_cmdtuples($this->Query_ID);
	}

	function num_rows() {
		return pg_numrows($this->Query_ID);
	}

	function num_fields() {
		return pg_numfields($this->Query_ID);
	}

	function nf() {
		return $this->num_rows();
	}

	function np() {
		print $this->num_rows();
	}

	function f($Name) {
		if (isset($this->Record[$Name])) {
			return $this->Record[$Name];
		}
	}

	function p($Name) {
		if (isset($this->Record[$Name])) {
			print $this->Record[$Name];
		}
	}
  
	function halt($msg) {
		printf("</td></tr></table><b>Database error:</b> %s<br>\n", $msg);
		printf("<b>PostgreSQL Error</b>: %s (%s)<br>\n",
			$this->Errno,
			$this->Error);
		die("Session halted.");
	}

	function table_names() {
		$this->query("select relname from pg_class where relkind = 'r' and not relname like 'pg_%'");
		$i=0;
		while ($this->next_record())
		{
			$return[$i]["table_name"]= $this->f(0);
			$return[$i]["tablespace_name"]=$this->Database;
			$return[$i]["database"]=$this->Database;
			$i++;
		}
		return $return;
	}
}

class PGM_Sql extends DB_Sql {

	/**
	* Method to be invoked to begin a transaction.
	* @access public
	* @return void
	*/
	function begin() {
		ignore_user_abort(1);
		$this->query("BEGIN");
	}

	/**
	* Method to be invoked to end a transaction.
	* @access public
	* @return void
	*/
	function commit() {
		$this->query("COMMIT");
		ignore_user_abort(0);
	}

	/**
	* This method addresses LEFT JOINs, especially the ones involving I18N tables.
	*
	* It allows to provide both DB astraction (as an example, really different
	* queries have to be used on PostgreSQL <= 7.0 and on MySQL 3.23 to perform
	* LEFT JOINs) and to simplify (and, hence, lighten) the query
	* when LEFT JOINs with I18N tables are not needed, i.e.,
	* when the current language coincides with the base language,
	* i.e. the language of the base, non I18N, tables.
	* Note: no check is performed about correctness/coherence of passed arguments.
	* @access public
	* @param string $base_lang The base language (of the shop);
	*   example: $CFG["base_lang"]
	* @param array $left_join_table N-elements array of strings,
	*   each one identifying a table and its respective alias;
	*   this array lists tables involved in LEFT JOINs for the query at hand;
	*   example: array("products p", "products_i18n l")
	* @param array $left_join_fields (N-1)-elements array of arrays;
	*   the i-th array concerns the LEFT JOIN between the i-th and the (i+1)-th
	*   tables of the $left_join_table array;
	*   each array consists of 4 fields, i.e. the left and the right field
	*   involved in the LEFT JOIN, a boolean field (0 or 1) indicating if
	*   the language is involved in the LEFT JOIN clause, and a string indicating
	*   the I18N language; the resulting query is simpler if this last field,
	*   i.e. the I18N language, is equal to the base language, i.e. $base_lang;
	*   example: array(array("p.id", "l.product_id", 1, $SESSION["lang"]))
	* @param array $left_join_wanted_field (N-1)-elements array of arrays;
	*   the i-th array concerns fields and respective aliases
	*   of the (i+1)-th table of the $left_join_table array;
	*   each array lists fields that have to be returned by the query;
	*   example: array(array("l.name AS lname",  "l.description AS ldescription"))
	* @param string $other_fields it lists other fields that must be used
	*   in the SELECT clause, both for tables that are involved in the LEFT JOINs
	*   and for tables that are not;
	*   example: "p.id, p.name, b.name AS bname, p.price, i.iva, p.discount, p.discqty, p.weight, p.special_level, p.description, p.thumbtype, p.thumbwidth, p.thumbheight"
	* @param string $other_tables it lists other tables (and respective aliases)
	*   that are not involved in the LEFT JOINs but must be used in the FROM clause;
	*   example: "brands b, iva i"
	* @param string $additional_where_clause conditions to be used in the WHERE
	*   clause in addition to the LEFT JOINs conditions;
	*   example: "p.brand_id = b.id AND p.iva_id = i.id AND special_flag = 1"
	* @param string $order_by_clause the ORDER BY clause; it can be an empty string;
	*   example: "special_level ASC"
	* @param array $limits two-fields array used for the LIMIT clause;
	*   it provides $limit_offset and $limit_numitems, i.e. the first element
	*   to be returned by the query and the maximum number of elements
	*   to be returned by the query; it can be an empty array;
	*   example: array()
	*   example: array($first, $limit)
	* @return Query_ID
	*/
	function pgm_lang_join_query(
		$base_lang,
		$left_join_table,
		$left_join_fields,
		$left_join_wanted_field,
		$other_fields,
		$other_tables,
		$additional_where_clause,
		$order_by_clause,
		$limits
	) {
		$num_join_tables = count($left_join_table);

		for ($i=0; $i<$num_join_tables; $i++) {
			$foobar = explode(" ", $left_join_table[$i]);
			$left_join_table_name[$i] = $foobar[0];
			$left_join_table_alias[$i] = $foobar[1];
		}
		for ($i=0; $i<$num_join_tables-1; $i++) {
			$left_join_left_field[$i] = $left_join_fields[$i][0];
			$left_join_right_field[$i] = $left_join_fields[$i][1];
			$lang_array[$i] = $left_join_fields[$i][2];
			if ($lang_array[$i]) {
				$lang[$i] = $left_join_fields[$i][3];
			}
			$counter = count($left_join_wanted_field[$i]);
			for ($cnt=0; $cnt<$counter; $cnt++) {
				$foobar = explode(" AS ", $left_join_wanted_field[$i][$cnt]);
				$left_join_wanted_field_name[$i][$cnt] = $foobar[0];
				$left_join_wanted_field_alias[$i][$cnt] = $foobar[1];
			}
		}

		$union_query = "";

		$lang_array[$num_join_tables-1] = "";
		$lang[$num_join_tables-1] = "";
		for ($query_count=0; $query_count<$num_join_tables; $query_count++) {

			if (!($lang_array[$query_count] && $lang[$query_count] == $base_lang))
			{
			$select_clause[$query_count] = "";
			for ($i=0; $i<$query_count; $i++) {
				$counter = count($left_join_wanted_field[$i]);
				for ($cnt=0; $cnt<$counter; $cnt++) {
					$select_clause[$query_count] .= ", '' AS " . $left_join_wanted_field_alias[$i][$cnt];
				}
			}
			for ($i=$query_count; $i<$num_join_tables-1; $i++) {
				$counter = count($left_join_wanted_field[$i]);
				for ($cnt=0; $cnt<$counter; $cnt++) {
					$select_clause[$query_count] .= ", " . $left_join_wanted_field[$i][$cnt];
				}
			}
			if ($other_fields != "") {
				$select_clause[$query_count] .= ", " . $other_fields;
			}
			$select_clause[$query_count] = "SELECT " . substr($select_clause[$query_count], 2);

			$from_clause[$query_count] = " FROM " . $left_join_table[0];
			$where_clause[$query_count] = "";
			for ($i=$query_count+1; $i<$num_join_tables; $i++) {
				$from_clause[$query_count] .= ", " . $left_join_table[$i];
				$foobar = ($left_join_table_alias[$i] != "") ? $left_join_table_alias[$i] : $left_join_table_name[$i];
				$where_clause[$query_count] .= " AND " . $left_join_left_field[$i-1] . " = " . $left_join_right_field[$i-1];
				if ($lang_array[$i-1]) {
					$where_clause[$query_count] .= " AND " . $foobar . ".lang = '" . $lang[$i-1] . "'";
				}
			}
			if ($other_tables != "") {
				$from_clause[$query_count] .= ", " . $other_tables;
			}
			if ($where_clause[$query_count] != "") {
				$where_clause[$query_count] = substr($where_clause[$query_count], 5);
			}
			if ($where_clause[$query_count] != "" && $additional_where_clause != "") {
				$where_clause[$query_count] = $where_clause[$query_count] . " AND " . $additional_where_clause;
			} else if ($where_clause[$query_count] == "") {
				$where_clause[$query_count] = $additional_where_clause;
			}
			if ($query_count>0) {
				if ($where_clause[$query_count] != "") {
					$where_clause[$query_count] .= " AND ";
				}
				$where_clause[$query_count] .= $left_join_left_field[$query_count-1] . " NOT IN ( SELECT " . $left_join_right_field[$query_count-1] . " FROM " . $left_join_table[$query_count];
				if ($lang_array[$query_count-1]) {
					$where_clause[$query_count] .= " WHERE " . $left_join_table_alias[$query_count] . ".lang = '" . $lang[$query_count-1] . "'";
				}
				$where_clause[$query_count] .= " )";
			}
			$where_clause[$query_count] = " WHERE " . $where_clause[$query_count];

			$query[$query_count] = $select_clause[$query_count] . "\n" . $from_clause[$query_count] . "\n" . $where_clause[$query_count] . "\n";
			$union_query .= "UNION ALL\n" . $query[$query_count];
			}
		}
		$union_query = substr($union_query, 10);

		if ($order_by_clause != "") {
			$order_by_clause = " ORDER BY " . $order_by_clause;
		}

		if (count($limits) == 2) {
			if ($limits[1] == 0) {
				$limits[1] = 1;	// workaround needed for PostgreSQL 7.0
			}
			$limit_clause = " LIMIT " . $limits[1] . " OFFSET " . $limits[0];
		} else {
			$limit_clause = "";
		}

		$union_query .= $order_by_clause . "\n" . $limit_clause;
		return $this->query($union_query);
	}
}
?>